import time
import random

from com.utils import dbutil
from com.lechebang import Setting
from com.lechebang.spider import LocationUtil


def uploadLocation(mysqlDB,dealerTable,dealerId=1):
    gaode_times=Setting.GODE_API_TIMES

    connection = dbutil.connectDB(mysqlDB)
    cursor = connection.cursor()
    #lcb_sql='select id, name, address from %s where latitude is null' % dealerTable
    sql = 'select a.id, a.store_name, a.address, b.pinyin, b.name from lcb_db.%s a left join lcb_comm_db.comm_place b ON a.place_id=b.id where a.latitude is null' % dealerTable
    updatesql="update %s set longitude='%s' ,latitude='%s' where id=%d"
    cursor.execute(sql)
    dealers = cursor.fetchall()
    tmp=1
    for dealer in dealers:
        if dealer[0]<dealerId:
            continue
        # print(dealer[0])
        # print("=======>"+dealer[1])
        location=LocationUtil.get_latAndLot(dealer[2],dealer[4])
        if location ==None:
            continue
        tmpsql=updatesql % (dealerTable,location[0], location[1], dealer[0])
        print(tmpsql)
        tmp = cursor.execute(tmpsql)
        connection.commit()

        if tmp<=0:
            print("======>执行SQL失败!!, sql:"+ tmpsql)
        time.sleep(random.uniform(1, 3))  # 随机休息几秒

        tmp+=1
        if tmp >=gaode_times:
            print("======>Goade API 次数今日已经用完,明日继续查询, now dealer: "+dealer)
            break


    cursor.close()
    connection.close()


def updateDistance(mysqlDB,dealerTable,dealerId=1):
    connection = dbutil.connectDB(mysqlDB)
    cursor = connection.cursor()
    # sql = 'select id, store_name, address from %s where latitude is null' % dealerTable
    sql='''
    SELECT a.id, a.store_name, a.address,a.longitude, a.latitude, a.lon_gd,a.lat_gd,d.center_long_gd,d.center_lat_gd,SUBSTRING_INDEX(b.name, '市', 1) AS cityname, b.pinyin
        FROM %s a
        LEFT JOIN lcb_comm_db.comm_place b ON a.place_id=b.id
        left join lcb_mars_db.crawler_city d on SUBSTRING_INDEX(b.name, '市', 1)=d.city_name
        WHERE a.is_test=0 AND a.deleted=0 AND a.store_type IN (0,5) and a.distance_to_center is null
    '''
    updatesql = "update %s set distance_to_center='%s' where id=%d"

    cursor.execute(sql % dealerTable)
    dealers = cursor.fetchall()

    for dealer in dealers:
        if dealer[3] is None or dealer[4] is None:
            continue
        if dealer[7] is not None and dealer[8] is not None:
            distance=LocationUtil.getDistance(dealer[3]+','+dealer[4],dealer[7]+','+dealer[8])
            print('=========>dealer:'+dealer[0]+'---distance:'+distance)
            tmpsql=updatesql % (dealerTable,distance,dealer[0])
            cursor.execute(tmpsql)
            connection.commit()
        time.sleep(random.uniform(1, 3))  # 随机休息几秒

    cursor.close()


if __name__ == '__main__':
    uploadLocation()
